using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public static partial class VideoPointService
    {
        public static bool AddVideoPoint(VideoPoint videoPoint)
        {
            string sql =
                "INSERT VideoPoint (Point, VideoID, IP)" +
                "VALUES (@Point, @VideoID, @IP)";

            sql += " ; SELECT @@IDENTITY";


            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@Point", videoPoint.Point),
				new SqlParameter("@VideoID", videoPoint.VideoID),
				new SqlParameter("@IP", videoPoint.IP)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }


        public static bool DeleteVideoPointByID(int iD)
        {
            string sql = "DELETE VideoPoint WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public static bool ModifyVideoPoint(VideoPoint videoPoint)
        {
            string sql =
                "UPDATE VideoPoint " +
                "SET " +
                    "Point = @Point, " +
                    "VideoID = @VideoID, " +
                    "IP = @IP " +
                "WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", videoPoint.ID),
				new SqlParameter("@Point", videoPoint.Point),
				new SqlParameter("@VideoID", videoPoint.VideoID),
				new SqlParameter("@IP", videoPoint.IP)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static IList<VideoPoint> GetAllVideoPoints()
        {
            string sqlAll = "SELECT * FROM VideoPoint";
            return GetVideoPointsBySql(sqlAll);
        }

        public static int GetVideoPointsCountByVideoID(int videoID)
        {
            string sqlAll = "SELECT count(*) FROM VideoPoint where VideoID=@VideoID";
            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@VideoID", videoID)
			};

            int i = Convert.ToInt32(DBHelper.ExecuteScalar(CommandType.Text, sqlAll, para));
            return i;
        }

        public static float GetVideoPointsSumByVideoID(int videoID)
        {
            string sqlAll = "SELECT isnull(sum(isnull(Point,0)),0) FROM VideoPoint where VideoID=@VideoID";
            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@VideoID", videoID)
			};

            float i = Convert.ToSingle(DBHelper.ExecuteScalar(CommandType.Text, sqlAll, para));
            return i;
        }

        public static bool GetVideoPointsByIP(string ip, int videoID)
        {
            string sqlAll = "SELECT ID FROM VideoPoint where IP=@IP and VideoID=@VideoID";
            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@IP", ip),
                new SqlParameter("@VideoID", videoID)
			};

            int i = Convert.ToInt32(DBHelper.ExecuteScalar(CommandType.Text, sqlAll, para));

            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static VideoPoint GetVideoPointByID(int iD)
        {
            string sql = "SELECT * FROM VideoPoint WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            VideoPoint videoPoint = new VideoPoint();

            foreach (DataRow dr in temp.Rows)
            {
                videoPoint.ID = (int)dr["ID"];
                videoPoint.Point = (double)dr["Point"];
                videoPoint.VideoID = (int)dr["VideoID"];
                videoPoint.IP = (string)dr["IP"];

            }
            return videoPoint;

        }


        private static IList<VideoPoint> GetVideoPointsBySql(string safeSql)
        {
            List<VideoPoint> list = new List<VideoPoint>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, null))
            {
                while (dr.Read())
                {
                    VideoPoint videoPoint = new VideoPoint();

                    videoPoint.ID = (int)dr["ID"];
                    videoPoint.Point = (double)dr["Point"];
                    videoPoint.VideoID = (int)dr["VideoID"];
                    videoPoint.IP = (string)dr["IP"];

                    list.Add(videoPoint);
                }
            }
            return list;
        }

        private static IList<VideoPoint> GetVideoPointsBySql(string safeSql, params SqlParameter[] para)
        {
            List<VideoPoint> list = new List<VideoPoint>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, para))
            {
                while (dr.Read())
                {
                    VideoPoint videoPoint = new VideoPoint();

                    videoPoint.ID = (int)dr["ID"];
                    videoPoint.Point = (double)dr["Point"];
                    videoPoint.VideoID = (int)dr["VideoID"];
                    videoPoint.IP = (string)dr["IP"];

                    list.Add(videoPoint);
                }
            }
            return list;
        }
    }
}
